<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>07 【单行函数】 | Bin</title>
    <meta name="description" content="帝彬">
    <link rel="stylesheet" href="/assets/style.9f8bf5c4.css">
    <link rel="modulepreload" href="/assets/app.82d46cfc.js">
    <link rel="modulepreload" href="/assets/mysql_07.md.3f58b4d2.lean.js">
    <link rel="prefetch" href="/assets/chunks/VPAlgoliaSearchBox.6ebb7441.js">
    <link rel="icon" href="/logo.svg">
  <link rel="preload" href="/assets/inter-latin.7b37fe23.woff2" as="font" type="font/woff2" crossorigin="anonymous">
  <script>(()=>{const e=localStorage.getItem("vue-theme-appearance");(!e||e==="auto"?window.matchMedia("(prefers-color-scheme: dark)").matches:e==="dark")&&document.documentElement.classList.add("dark")})();</script>
  <meta name="generator" content="qq1974892005">
  <link rel="icon" href="/images/logo.svg">
  <meta name="name" content="Bin">
  <script src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script>
  <script src="https://hm.baidu.com/hm.js?d186385b8ee12dbef7031da2e9c9eee3"></script>
  <script src="/public/tinymce/tinymce.js"></script>
  <meta name="twitter:title" content="07 【单行函数】 | Bin">
  <meta property="og:title" content="07 【单行函数】 | Bin">
  </head>
  <body>
    <div id="app"><div class="VPApp" data-v-23306c50><!--[--><span tabindex="-1" data-v-3785d3a7></span><a href="#VPContent" class="VPSkipLink visually-hidden" data-v-3785d3a7> Skip to content </a><!--]--><!----><!--[--><div></div><!--]--><header class="VPNav nav-bar" data-v-23306c50 data-v-7d674ffc><div class="VPNavBar" data-v-7d674ffc data-v-96a69ce8><div class="container" data-v-96a69ce8><a class="VPNavBarTitle" href="/" data-v-96a69ce8 data-v-1f5e00a8><!--[--><img class="logo" src="/logo.svg" alt="bin" srcset="" data-v-1f5e00a8><span class="text" data-v-1f5e00a8>Bin</span><!--]--></a><div class="content" data-v-96a69ce8><!----><nav aria-labelledby="main-nav-aria-label" class="VPNavBarMenu menu" data-v-96a69ce8 data-v-9072bcde><span id="main-nav-aria-label" class="visually-hidden" data-v-9072bcde>Main Navigation</span><!--[--><!--[--><a class="vt-link link VPNavBarMenuLink" href="/yuanshen/" data-v-9072bcde data-v-6bdae1a3><!--[-->原神大地图<!--]--><!----></a><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">前端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/js/01.html"><!--[-->JavaScript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ajax/01.html"><!--[-->ajax<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ES6/01.html"><!--[-->ES6<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/echarts/01.html"><!--[-->echarts<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/typescript/01.html"><!--[-->typescript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue2/01.html"><!--[-->vue2<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue3/01.html"><!--[-->vue3<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/git/01.html"><!--[-->git<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">服务端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/nodejs/01.html"><!--[-->nodejs<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mysql/01.html"><!--[-->mysql<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mongodb/01.html"><!--[-->mongodb<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">CSS提升 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/less/01.html"><!--[-->less<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/sass/01.html"><!--[-->sass<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/tailwindcss/01.html"><!--[-->tailwindcss<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">集合 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/React/"><!--[-->React<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/ReactNative/"><!--[-->ReactNative<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vue/"><!--[-->Vue<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuex/"><!--[-->Vuex<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuerouter/"><!--[-->Vue-Router<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">参数 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/about/canshu.html"><!--[-->常用参数<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/dayin.html"><!--[-->打印机<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/chahuo.html"><!--[-->市场查货<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="https://sunlogin.oray.com/download" target="_blank" rel="noopener noreferrer"><!--[-->向日葵远程下载<!--]--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" height="24px" viewbox="0 0 24 24" width="24px" class="vt-link-icon"><path d="M0 0h24v24H0V0z" fill="none"></path><path d="M9 5v2h6.59L4 18.59 5.41 20 17 8.41V15h2V5H9z"></path></svg></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">其他 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/test/"><!--[-->日志<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/test/muban.html"><!--[-->富文本模板<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--]--></nav><div class="VPNavBarAppearance appearance" data-v-96a69ce8 data-v-d5f66188><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-d5f66188><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div><div class="vt-social-links VPNavBarSocialLinks social-links" data-v-96a69ce8 data-v-8b9a7f88><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div><div class="vt-flyout VPNavBarExtra extra" data-v-96a69ce8 data-v-b3e218c4><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false" aria-label="extra navigation"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-icon"><circle cx="12" cy="12" r="2"></circle><circle cx="19" cy="12" r="2"></circle><circle cx="5" cy="12" r="2"></circle></svg></button><div class="vt-flyout-menu"><div class="vt-menu"><!----><!--[--><!--[--><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><p class="vt-menu-label" data-v-b3e218c4>Appearance</p><div class="vt-menu-action action" data-v-b3e218c4><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-b3e218c4><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div></div></div><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><div class="vt-social-links social-links" data-v-b3e218c4><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div></div></div><!--]--><!--]--></div></div></div><button type="button" class="vt-hamburger VPNavBarHamburger hamburger" aria-label="mobile navigation" aria-expanded="false" aria-controls="VPNavScreen" data-v-96a69ce8 data-v-c78f86de><span class="vt-hamburger-container"><span class="vt-hamburger-top"></span><span class="vt-hamburger-middle"></span><span class="vt-hamburger-bottom"></span></span></button></div></div></div><!----></header><div class="VPLocalNav" data-v-23306c50 data-v-7cd5ed0a><button class="menu" aria-expanded="false" aria-controls="VPSidebarNav" data-v-7cd5ed0a><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="menu-icon" data-v-7cd5ed0a><path d="M17,11H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,11,17,11z"></path><path d="M21,7H3C2.4,7,2,6.6,2,6s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,7,21,7z"></path><path d="M21,15H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,15,21,15z"></path><path d="M17,19H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,19,17,19z"></path></svg><span class="menu-text" data-v-7cd5ed0a>Menu</span></button><a class="top-link" href="#" data-v-7cd5ed0a>Return to top</a></div><aside class="VPSidebar" data-v-23306c50 data-v-22052bdb><nav id="VPSidebarNav" aria-labelledby="sidebar-aria-label" tabindex="-1" data-v-22052bdb><!--[--><!--]--><span id="sidebar-aria-label" class="visually-hidden" data-v-22052bdb>Sidebar Navigation</span><!--[--><div class="group" data-v-22052bdb><section class="VPSidebarGroup" data-v-22052bdb data-v-59eaa146><div class="title" data-v-59eaa146><h2 class="active title-text" data-v-59eaa146>mysql</h2></div><!--[--><a class="link" href="/mysql/01.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>01 【数据库概述】</p></a><a class="link" href="/mysql/02.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>02 【MySQL基本使用】</p></a><a class="link" href="/mysql/03.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>03 【基本的SELECT语句】</p></a><a class="link" href="/mysql/04.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>04 【运算符】</p></a><a class="link" href="/mysql/05.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>05 【排序与分页】</p></a><a class="link" href="/mysql/06.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>06 【多表查询】</p></a><a class="link active" href="/mysql/07.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>07 【单行函数】</p></a><a class="link" href="/mysql/08.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>08 【聚合函数与分组查询】</p></a><a class="link" href="/mysql/09.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>09 【子查询】</p></a><a class="link" href="/mysql/10.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>10 【创建和管理表】</p></a><a class="link" href="/mysql/11.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>11 【数据处理之增删改】</p></a><!--]--></section></div><!--]--><!--[--><!--]--></nav></aside><div id="VPContent" class="VPContent has-sidebar" data-v-23306c50 data-v-790151bc><div class="VPContentDoc has-aside has-sidebar" data-v-790151bc data-v-4fe9b7bd><div class="container" data-v-4fe9b7bd><div class="aside" data-v-4fe9b7bd><div class="aside-container" data-v-4fe9b7bd><!--[--><!--]--><div class="VPContentDocOutline" data-v-4fe9b7bd data-v-aa0e2252><div class="outline-marker" data-v-aa0e2252></div><div class="outline-title" data-v-aa0e2252>On this page</div><nav aria-labelledby="doc-outline-aria-label" data-v-aa0e2252><span id="doc-outline-aria-label" class="visually-hidden" data-v-aa0e2252>Table of Contents for current page</span><ul class="root" data-v-aa0e2252><!--[--><li style="" data-v-aa0e2252><a class="outline-link" href="#_1-函数的理解" data-v-aa0e2252>1. 函数的理解</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_2-数值函数" data-v-aa0e2252>2. 数值函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_3-字符串函数" data-v-aa0e2252>3. 字符串函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_4-日期和时间函数" data-v-aa0e2252>4.日期和时间函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_5-流程控制函数" data-v-aa0e2252>5.流程控制函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_6-加密与解密函数" data-v-aa0e2252>6. 加密与解密函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_7-mysql信息函数" data-v-aa0e2252>7. MySQL信息函数</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_8-练习" data-v-aa0e2252>8.练习</a><!----></li><!--]--></ul></nav></div><!--[--><!--[--><!--[--><div><a class="sponsors-aside-text" href="/">联系客服</a><img src="/images/qrcode.png"></div><!--]--><!--]--><!--]--><div class="VPCarbonAds" data-v-4fe9b7bd><img src="/logo.svg" alt="" srcset=""></div><!--[--><!--[--><!--[--><h1 class="tagline" data-v-3916b2f0><span class="accent" data-v-3916b2f0>Bin</span><br data-v-3916b2f0>QQ - 1974892005 </h1><!--]--><!--]--><!--]--></div></div><div class="content" data-v-4fe9b7bd><!--[--><!--]--><main data-v-4fe9b7bd><div style="position:relative;" class="vt-doc mysql" data-v-4fe9b7bd><div><h1 id="_07-【单行函数】" tabindex="-1">07 【单行函数】 <a class="header-anchor" href="#_07-【单行函数】" aria-hidden="true">#</a></h1><h2 id="_1-函数的理解" tabindex="-1">1. 函数的理解 <a class="header-anchor" href="#_1-函数的理解" aria-hidden="true">#</a></h2><h3 id="_1-1-什么是函数" tabindex="-1">1.1 什么是函数 <a class="header-anchor" href="#_1-1-什么是函数" aria-hidden="true">#</a></h3><p>函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既<code>提高了代码效率</code>，又<code>提高了可维护性</code>。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地<code>提高用户对数据库的管理效率</code>。</p><p><img src="https://i0.hdslb.com/bfs/album/460077e65245adcb0a1a7441d28b59a6be497978.png" alt=""></p><p>从函数定义的角度出发，我们可以将函数分成<code>内置函数</code>和<code>自定义函数</code>。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。</p><h3 id="_1-2-不同dbms函数的差异" tabindex="-1">1.2 不同DBMS函数的差异 <a class="header-anchor" href="#_1-2-不同dbms函数的差异" aria-hidden="true">#</a></h3><p>我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS。<strong>DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。<strong>实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着</strong>采用 SQL 函数的代码可移植性是很差的</strong>，因此在使用函数的时候需要特别注意。</p><h3 id="_1-3-mysql的内置函数及分类" tabindex="-1">1.3 MySQL的内置函数及分类 <a class="header-anchor" href="#_1-3-mysql的内置函数及分类" aria-hidden="true">#</a></h3><p>MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。</p><p>MySQL提供的内置函数从<code>实现的功能角度</code>可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：<code>单行函数</code>、<code>聚合函数（或分组函数）</code>。</p><p><strong>两种SQL函数</strong></p><p><img src="https://i0.hdslb.com/bfs/album/b6afce5dae8f5cdd0952d926527d9fe489a0ff28.png" alt=""></p><p><strong>单行函数</strong></p><ul><li>操作数据对象</li><li>接受参数返回一个结果</li><li><strong>只对一行进行变换</strong></li><li><strong>每行返回一个结果</strong></li><li>可以嵌套</li><li>参数可以是一列或一个值</li></ul><h2 id="_2-数值函数" tabindex="-1">2. 数值函数 <a class="header-anchor" href="#_2-数值函数" aria-hidden="true">#</a></h2><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>ABS(x)</td><td>返回x的绝对值</td></tr><tr><td>SIGN(X)</td><td>返回X的符号。正数返回1，负数返回-1，0返回0</td></tr><tr><td>PI()</td><td>返回圆周率的值</td></tr><tr><td>CEIL(x)，CEILING(x)</td><td>返回大于或等于某个值的最小整数</td></tr><tr><td>FLOOR(x)</td><td>返回小于或等于某个值的最大整数</td></tr><tr><td>LEAST(e1,e2,e3…)</td><td>返回列表中的最小值</td></tr><tr><td>GREATEST(e1,e2,e3…)</td><td>返回列表中的最大值</td></tr><tr><td>MOD(x,y)</td><td>返回X除以Y后的余数</td></tr><tr><td>RAND()</td><td>返回0~1的随机值</td></tr><tr><td>RAND(x)</td><td>返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数</td></tr><tr><td>ROUND(x)</td><td>返回一个对x的值进行四舍五入后，最接近于X的整数</td></tr><tr><td>ROUND(x,y)</td><td>返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位</td></tr><tr><td>TRUNCATE(x,y)</td><td>返回数字x截断为y位小数的结果</td></tr><tr><td>SQRT(x)</td><td>返回x的平方根。当X的值为负数时，返回NULL</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">ABS</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">123</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">ABS</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">SIGN</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">SIGN</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">43</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">PI</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,CEIL(</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">CEILING</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">43</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">FLOOR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#82AAFF;">FLOOR</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">43</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">),MOD(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">5</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/913d5aa8e47793234e68e4461a3705557f335f37.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">RAND</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#82AAFF;">RAND</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#82AAFF;">RAND</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">RAND</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">RAND</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">RAND</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/a4b859c2c65611772de8dcfcd3018f28e7610bb0.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">ROUND</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">33</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">ROUND</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">343</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">ROUND</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">324</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">),</span><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">66</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">),</span><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">66</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/31b3b33aaf760eded4ee197782c1e0640064a110.png" alt=""></p><h2 id="_3-字符串函数" tabindex="-1">3. 字符串函数 <a class="header-anchor" href="#_3-字符串函数" aria-hidden="true">#</a></h2><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>ASCII(S)</td><td>返回字符串S中的第一个字符的ASCII码值</td></tr><tr><td>CHAR_LENGTH(s)</td><td>返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同</td></tr><tr><td>LENGTH(s)</td><td>返回字符串s的字节数，和字符集有关</td></tr><tr><td>CONCAT(s1,s2,......,sn)</td><td>连接s1,s2,......,sn为一个字符串</td></tr><tr><td>CONCAT_WS(x, s1,s2,......,sn)</td><td>同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x</td></tr><tr><td>INSERT(str, idx, len, replacestr)</td><td>将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr</td></tr><tr><td>REPLACE(str, a, b)</td><td>用字符串b替换字符串str中所有出现的字符串a</td></tr><tr><td>UPPER(s) 或 UCASE(s)</td><td>将字符串s的所有字母转成大写字母</td></tr><tr><td>LOWER(s) 或LCASE(s)</td><td>将字符串s的所有字母转成小写字母</td></tr><tr><td>LEFT(str,n)</td><td>返回字符串str最左边的n个字符</td></tr><tr><td>RIGHT(str,n)</td><td>返回字符串str最右边的n个字符</td></tr><tr><td>LPAD(str, len, pad)</td><td>用字符串pad对str最左边进行填充，直到str的长度为len个字符</td></tr><tr><td>RPAD(str ,len, pad)</td><td>用字符串pad对str最右边进行填充，直到str的长度为len个字符</td></tr><tr><td>LTRIM(s)</td><td>去掉字符串s左侧的空格</td></tr><tr><td>RTRIM(s)</td><td>去掉字符串s右侧的空格</td></tr><tr><td>TRIM(s)</td><td>去掉字符串s开始与结尾的空格</td></tr><tr><td>TRIM(s1 FROM s)</td><td>去掉字符串s开始与结尾的s1</td></tr><tr><td>TRIM(LEADING s1 FROM s)</td><td>去掉字符串s开始处的s1</td></tr><tr><td>TRIM(TRAILING s1 FROM s)</td><td>去掉字符串s结尾处的s1</td></tr><tr><td>REPEAT(str, n)</td><td>返回str重复n次的结果</td></tr><tr><td>SPACE(n)</td><td>返回n个空格</td></tr><tr><td>STRCMP(s1,s2)</td><td>比较字符串s1,s2的ASCII码值的大小</td></tr><tr><td>SUBSTR(s,index,len)</td><td>返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同</td></tr><tr><td>LOCATE(substr,str)</td><td>返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0</td></tr><tr><td>ELT(m,s1,s2,…,sn)</td><td>返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn</td></tr><tr><td>FIELD(s,s1,s2,…,sn)</td><td>返回字符串s在字符串列表中第一次出现的位置</td></tr><tr><td>FIND_IN_SET(s1,s2)</td><td>返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串</td></tr><tr><td>REVERSE(s)</td><td>返回s反转后的字符串</td></tr><tr><td>NULLIF(value1,value2)</td><td>比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1</td></tr></tbody></table><blockquote><p>注意：MySQL中，字符串的位置是从1开始的。</p></blockquote><p>常用函数：</p><table><thead><tr><th style="text-align:left;">函数</th><th style="text-align:left;">功能</th></tr></thead><tbody><tr><td style="text-align:left;">CONCAT(s1, s2, …, sn)</td><td style="text-align:left;">字符串拼接，将s1, s2, …, sn拼接成一个字符串</td></tr><tr><td style="text-align:left;">LOWER(str)</td><td style="text-align:left;">将字符串全部转为小写</td></tr><tr><td style="text-align:left;">UPPER(str)</td><td style="text-align:left;">将字符串全部转为大写</td></tr><tr><td style="text-align:left;">LPAD(str, n, pad)</td><td style="text-align:left;">左填充，用字符串pad对str的左边进行填充，达到n个字符串长度</td></tr><tr><td style="text-align:left;">RPAD(str, n, pad)</td><td style="text-align:left;">右填充，用字符串pad对str的右边进行填充，达到n个字符串长度</td></tr><tr><td style="text-align:left;">TRIM(str)</td><td style="text-align:left;">去掉字符串头部和尾部的空格</td></tr><tr><td style="text-align:left;">SUBSTRING(str, start, len)</td><td style="text-align:left;">返回从字符串str从start位置起的len个长度的字符串</td></tr><tr><td style="text-align:left;">REPLACE(column, source, replace)</td><td style="text-align:left;">替换字符串</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> FIELD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">hello</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">msm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">amma</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),FIND_IN_SET(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">hello,mm,amma</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#A6ACCD;">    </span><span style="color:#89DDFF;">-&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------------+-----------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| FIELD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">hello</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">msm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">amma</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) | FIND_IN_SET(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mm</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">hello,mm,amma</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------------+-----------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                                </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> |                                 </span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------------+-----------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">NULLIF</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">NULLIF</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+---------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#82AAFF;">NULLIF</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) | </span><span style="color:#82AAFF;">NULLIF</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+---------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">                    | mysql               |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+---------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>其它:</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- 拼接</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">CONCAT</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Hello</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">World</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 小写</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">LOWER</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Hello</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 大写</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">UPPER</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Hello</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 左填充</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> LPAD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">01</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">5</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">-</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 右填充</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> RPAD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">01</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">5</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">-</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 去除空格</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">TRIM</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;"> Hello World </span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- 切片（起始索引为1）</span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">SUBSTRING</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Hello World</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">5</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><h2 id="_4-日期和时间函数" tabindex="-1">4.日期和时间函数 <a class="header-anchor" href="#_4-日期和时间函数" aria-hidden="true">#</a></h2><p>常用函数：</p><table><thead><tr><th style="text-align:left;">函数</th><th style="text-align:left;">功能</th></tr></thead><tbody><tr><td style="text-align:left;">CURDATE()</td><td style="text-align:left;">返回当前日期</td></tr><tr><td style="text-align:left;">CURTIME()</td><td style="text-align:left;">返回当前时间</td></tr><tr><td style="text-align:left;">NOW()</td><td style="text-align:left;">返回当前日期和时间</td></tr><tr><td style="text-align:left;">YEAR(date)</td><td style="text-align:left;">获取指定date的年份</td></tr><tr><td style="text-align:left;">MONTH(date)</td><td style="text-align:left;">获取指定date的月份</td></tr><tr><td style="text-align:left;">DAY(date)</td><td style="text-align:left;">获取指定date的日期</td></tr><tr><td style="text-align:left;">DATE_ADD(date, INTERVAL expr type)</td><td style="text-align:left;">返回一个日期/时间值加上一个时间间隔expr后的时间值</td></tr><tr><td style="text-align:left;">DATEDIFF(date1, date2)</td><td style="text-align:left;">返回起始时间date1和结束时间date2之间的天数</td></tr></tbody></table><h3 id="_4-1-获取日期、时间" tabindex="-1">4.1 获取日期、时间 <a class="header-anchor" href="#_4-1-获取日期、时间" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td><strong>CURDATE()</strong> ，CURRENT_DATE()</td><td>返回当前日期，只包含年、月、日</td></tr><tr><td><strong>CURTIME()</strong> ， CURRENT_TIME()</td><td>返回当前时间，只包含时、分、秒</td></tr><tr><td><strong>NOW()</strong> / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()</td><td>返回当前系统日期和时间</td></tr><tr><td>UTC_DATE()</td><td>返回UTC（世界标准时间）日期</td></tr><tr><td>UTC_TIME()</td><td>返回UTC（世界标准时间）时间</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,CURTIME</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#C792EA;">SYSDATE</span><span style="color:#89DDFF;">()+</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">,UTC_DATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,UTC_DATE</span><span style="color:#89DDFF;">()+</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">,UTC_TIME</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,UTC_TIME</span><span style="color:#89DDFF;">()+</span><span style="color:#F78C6C;">0</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/36115b0698b2c23ef8506d264fa316a8413f3dba.png" alt=""></p><h3 id="_4-2-日期与时间戳的转换" tabindex="-1">4.2 日期与时间戳的转换 <a class="header-anchor" href="#_4-2-日期与时间戳的转换" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>UNIX_TIMESTAMP()</td><td>以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() -&gt;1634348884</td></tr><tr><td>UNIX_TIMESTAMP(date)</td><td>将时间date以UNIX时间戳的形式返回。</td></tr><tr><td>FROM_UNIXTIME(timestamp)</td><td>将UNIX时间戳的时间转换为普通格式的时间</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> UNIX_TIMESTAMP(</span><span style="color:#F78C6C;">now</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| UNIX_TIMESTAMP(</span><span style="color:#F78C6C;">now</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|            </span><span style="color:#F78C6C;">1576380910</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> UNIX_TIMESTAMP(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| UNIX_TIMESTAMP(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                </span><span style="color:#F78C6C;">1576339200</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> UNIX_TIMESTAMP(CURTIME</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| UNIX_TIMESTAMP(CURTIME</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                </span><span style="color:#F78C6C;">1576380969</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> UNIX_TIMESTAMP(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2011-11-11 11:11:11</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| UNIX_TIMESTAMP(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2011-11-11 11:11:11</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                            </span><span style="color:#F78C6C;">1320981071</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br></div></div><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> FROM_UNIXTIME(</span><span style="color:#F78C6C;">1576380910</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| FROM_UNIXTIME(</span><span style="color:#F78C6C;">1576380910</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2019</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">12</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">11</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">35</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">       |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="_4-3-获取月份、星期、星期数、天数等函数" tabindex="-1">4.3 获取月份、星期、星期数、天数等函数 <a class="header-anchor" href="#_4-3-获取月份、星期、星期数、天数等函数" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>YEAR(date) / MONTH(date) / DAY(date)</td><td>返回具体的日期值</td></tr><tr><td>HOUR(time) / MINUTE(time) / SECOND(time)</td><td>返回具体的时间值</td></tr><tr><td>MONTHNAME(date)</td><td>返回月份：January，...</td></tr><tr><td>DAYNAME(date)</td><td>返回星期几：MONDAY，TUESDAY.....SUNDAY</td></tr><tr><td>WEEKDAY(date)</td><td>返回周几，注意，周1是0，周2是1，。。。周日是6</td></tr><tr><td>QUARTER(date)</td><td>返回日期对应的季度，范围为1～4</td></tr><tr><td>WEEK(date) ， WEEKOFYEAR(date)</td><td>返回一年中的第几周</td></tr><tr><td>DAYOFYEAR(date)</td><td>返回日期是一年中的第几天</td></tr><tr><td>DAYOFMONTH(date)</td><td>返回日期位于所在月份的第几天</td></tr><tr><td>DAYOFWEEK(date)</td><td>返回周几，注意：周日是1，周一是2，。。。周六是7</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">YEAR</span><span style="color:#A6ACCD;">(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">MONTH</span><span style="color:#A6ACCD;">(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">DAY</span><span style="color:#A6ACCD;">(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">HOUR(CURTIME</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),MINUTE(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),SECOND(</span><span style="color:#C792EA;">SYSDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/9ff4aeeaba2cac2c634b2fb30be13ce367215375.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> MONTHNAME(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-26</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),DAYNAME(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-26</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span><span style="color:#F78C6C;">WEEKDAY</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-26</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">QUARTER(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),WEEK(CURDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),DAYOFYEAR(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">DAYOFMONTH(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),DAYOFWEEK(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/d098422d3d7138127d83474ef4e62f2eece71a25.png" alt=""></p><h3 id="_4-4-日期的操作函数" tabindex="-1">4.4 日期的操作函数 <a class="header-anchor" href="#_4-4-日期的操作函数" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>EXTRACT(type FROM date)</td><td>返回指定日期中特定的部分，type指定返回的值</td></tr></tbody></table><p>EXTRACT(type FROM date)函数中type的取值与含义：</p><p><img src="https://i0.hdslb.com/bfs/album/17c2325d4632779a6869f73ea1f077ce3f24688a.png" alt=""></p><p><img src="https://i0.hdslb.com/bfs/album/c7d9e6362d7558acded25f49e280639bfb6bdb83.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> EXTRACT(MINUTE </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),EXTRACT( WEEK </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">EXTRACT( QUARTER </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),EXTRACT( MINUTE_SECOND </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_4-5-时间和秒钟转换的函数" tabindex="-1">4.5 时间和秒钟转换的函数 <a class="header-anchor" href="#_4-5-时间和秒钟转换的函数" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>TIME_TO_SEC(time)</td><td>将 time 转化为秒并返回结果值。转化的公式为：<code>小时*3600+分钟*60+秒</code></td></tr><tr><td>SEC_TO_TIME(seconds)</td><td>将 seconds 描述转化为包含小时、分钟和秒的时间</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> TIME_TO_SEC(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| TIME_TO_SEC(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|               </span><span style="color:#F78C6C;">78774</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> SEC_TO_TIME(</span><span style="color:#F78C6C;">78774</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| SEC_TO_TIME(</span><span style="color:#F78C6C;">78774</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">21</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">52</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">54</span><span style="color:#A6ACCD;">            |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><h3 id="_4-6-计算日期和时间的函数" tabindex="-1">4.6 计算日期和时间的函数 <a class="header-anchor" href="#_4-6-计算日期和时间的函数" aria-hidden="true">#</a></h3><p><strong>第1组：</strong></p><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)</td><td>返回与给定日期时间相差INTERVAL时间段的日期时间</td></tr><tr><td>DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)</td><td>返回与date相差INTERVAL时间间隔的日期</td></tr></tbody></table><p>上述函数中type的取值：</p><p><img src="https://i0.hdslb.com/bfs/album/1ebc8fa0e3eb1a2142732e6df2a70fe1131ecf5a.png" alt=""></p><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> DATE_ADD(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, INTERVAL </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">DAY</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col1,DATE_ADD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-21 23:32:12</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> SECOND) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col2,</span></span>
<span class="line"><span style="color:#A6ACCD;">ADDDATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-21 23:32:12</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> SECOND) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col3,</span></span>
<span class="line"><span style="color:#A6ACCD;">DATE_ADD(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-21 23:32:12</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1_1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> MINUTE_SECOND) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col4,</span></span>
<span class="line"><span style="color:#A6ACCD;">DATE_ADD(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, INTERVAL </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">YEAR</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col5, #可以是负数</span></span>
<span class="line"><span style="color:#A6ACCD;">DATE_ADD(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, INTERVAL </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1_1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> YEAR_MONTH) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col6 #需要单引号</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> DATE_SUB(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-01-21</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#F78C6C;">31</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">DAY</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col1,</span></span>
<span class="line"><span style="color:#A6ACCD;">SUBDATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-01-21</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#F78C6C;">31</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">DAY</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col2,</span></span>
<span class="line"><span style="color:#A6ACCD;">DATE_SUB(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-01-21 02:01:01</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,INTERVAL </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1 1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> DAY_HOUR) </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> col3</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p><strong>第2组：</strong></p><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>ADDTIME(time1,time2)</td><td>返回time1加上time2的时间。当time2为一个数字时，代表的是<code>秒</code>，可以为负数</td></tr><tr><td>SUBTIME(time1,time2)</td><td>返回time1减去time2后的时间。当time2为一个数字时，代表的是<code>秒</code>，可以为负数</td></tr><tr><td>DATEDIFF(date1,date2)</td><td>返回date1 - date2的日期间隔天数</td></tr><tr><td>TIMEDIFF(time1, time2)</td><td>返回time1 - time2的时间间隔</td></tr><tr><td>FROM_DAYS(N)</td><td>返回从0000年1月1日起，N天以后的日期</td></tr><tr><td>TO_DAYS(date)</td><td>返回日期date距离0000年1月1日的天数</td></tr><tr><td>LAST_DAY(date)</td><td>返回date所在月份的最后一天的日期</td></tr><tr><td>MAKEDATE(year,n)</td><td>针对给定年份与所在年份中的天数返回一个日期</td></tr><tr><td>MAKETIME(hour,minute,second)</td><td>将给定的小时、分钟和秒组合成时间并返回</td></tr><tr><td>PERIOD_ADD(time,n)</td><td>返回time加上n后的时间</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> ADDTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">20</span><span style="color:#A6ACCD;">),SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">30</span><span style="color:#A6ACCD;">),SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1:1:3</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span><span style="color:#82AAFF;">DATEDIFF</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-01</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">TIMEDIFF(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2021-10-25 22:10:10</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),FROM_DAYS(</span><span style="color:#F78C6C;">366</span><span style="color:#A6ACCD;">),TO_DAYS(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">0000-12-25</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">LAST_DAY(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),MAKEDATE(</span><span style="color:#82AAFF;">YEAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">),</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;">),MAKETIME(</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">21</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">),PERIOD_ADD(</span><span style="color:#F78C6C;">20200101010101</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> ADDTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">50</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| ADDTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">50</span><span style="color:#A6ACCD;">)  |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2019</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">12</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">22</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">17</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">47</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> ADDTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1:1:1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| ADDTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1:1:1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2019</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">12</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">18</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">46</span><span style="color:#A6ACCD;">     |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1:1:1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1:1:1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2019</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">12</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">21</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">23</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">50</span><span style="color:#A6ACCD;">     |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">-1:-1:-1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| SUBTIME(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">-1:-1:-1</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2019</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">12</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">22</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">25</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">11</span><span style="color:#A6ACCD;">        |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> warning (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> FROM_DAYS(</span><span style="color:#F78C6C;">366</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| FROM_DAYS(</span><span style="color:#F78C6C;">366</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">0001</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">     |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> MAKEDATE(</span><span style="color:#F78C6C;">2020</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| MAKEDATE(</span><span style="color:#F78C6C;">2020</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2020</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">       |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> MAKEDATE(</span><span style="color:#F78C6C;">2020</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| MAKEDATE(</span><span style="color:#F78C6C;">2020</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">32</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2020</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">02</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">        |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> MAKETIME(</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| MAKETIME(</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">        |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> PERIOD_ADD(</span><span style="color:#F78C6C;">20200101010101</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| PERIOD_ADD(</span><span style="color:#F78C6C;">20200101010101</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|               </span><span style="color:#F78C6C;">20200101010102</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> TO_DAYS(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| TO_DAYS(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|          </span><span style="color:#F78C6C;">737773</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br><span class="line-number">90</span><br><span class="line-number">91</span><br><span class="line-number">92</span><br><span class="line-number">93</span><br><span class="line-number">94</span><br></div></div><p>举例：查询 7 天内的新增用户数有多少？</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">COUNT</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;">) </span><span style="color:#F78C6C;">as</span><span style="color:#A6ACCD;"> num </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> new_user </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> TO_DAYS(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">)</span><span style="color:#89DDFF;">-</span><span style="color:#A6ACCD;">TO_DAYS(regist_time)</span><span style="color:#89DDFF;">&lt;=</span><span style="color:#F78C6C;">7</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_4-7-日期的格式化与解析" tabindex="-1">4.7 日期的格式化与解析 <a class="header-anchor" href="#_4-7-日期的格式化与解析" aria-hidden="true">#</a></h3><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>DATE_FORMAT(date,fmt)</td><td>按照字符串fmt格式化日期date值</td></tr><tr><td>TIME_FORMAT(time,fmt)</td><td>按照字符串fmt格式化时间time值</td></tr><tr><td>GET_FORMAT(date_type,format_type)</td><td>返回日期字符串的显示格式</td></tr><tr><td>STR_TO_DATE(str, fmt)</td><td>按照字符串fmt对str进行解析，解析为一个日期</td></tr></tbody></table><p>上述<code>非GET_FORMAT</code>函数中fmt参数常用的格式符：</p><table><thead><tr><th>格式符</th><th>说明</th><th>格式符</th><th>说明</th></tr></thead><tbody><tr><td>%Y</td><td>4位数字表示年份</td><td>%y</td><td>表示两位数字表示年份</td></tr><tr><td>%M</td><td>月名表示月份（January,....）</td><td>%m</td><td>两位数字表示月份（01,02,03。。。）</td></tr><tr><td>%b</td><td>缩写的月名（Jan.，Feb.，....）</td><td>%c</td><td>数字表示月份（1,2,3,...）</td></tr><tr><td>%D</td><td>英文后缀表示月中的天数（1st,2nd,3rd,...）</td><td>%d</td><td>两位数字表示月中的天数(01,02...)</td></tr><tr><td>%e</td><td>数字形式表示月中的天数（1,2,3,4,5.....）</td><td></td><td></td></tr><tr><td>%H</td><td>两位数字表示小数，24小时制（01,02..）</td><td>%h和%I</td><td>两位数字表示小时，12小时制（01,02..）</td></tr><tr><td>%k</td><td>数字形式的小时，24小时制(1,2,3)</td><td>%l</td><td>数字形式表示小时，12小时制（1,2,3,4....）</td></tr><tr><td>%i</td><td>两位数字表示分钟（00,01,02）</td><td>%S和%s</td><td>两位数字表示秒(00,01,02...)</td></tr><tr><td>%W</td><td>一周中的星期名称（Sunday...）</td><td>%a</td><td>一周中的星期缩写（Sun.，Mon.,Tues.，..）</td></tr><tr><td>%w</td><td>以数字表示周中的天数(0=Sunday,1=Monday....)</td><td></td><td></td></tr><tr><td>%j</td><td>以3位数字表示年中的天数(001,002...)</td><td>%U</td><td>以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天</td></tr><tr><td>%u</td><td>以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天</td><td></td><td></td></tr><tr><td>%T</td><td>24小时制</td><td>%r</td><td>12小时制</td></tr><tr><td>%p</td><td>AM或PM</td><td>%%</td><td>表示%</td></tr></tbody></table><p>GET_FORMAT函数中date_type和format_type参数取值如下：</p><p><img src="https://i0.hdslb.com/bfs/album/98ec1e2db77b46e29821bc3b32376745ce820f87.png" alt=""></p><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATE_FORMAT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%H:%i:%s</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">DATE_FORMAT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%H:%i:%s</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">22</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">57</span><span style="color:#A6ACCD;">:</span><span style="color:#F78C6C;">34</span><span style="color:#A6ACCD;">                        |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">09/01/2009</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%m/%d/%Y</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">20140422154706</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%Y%m%d%H%i%s</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2014-04-22 15:47:06</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%Y-%m-%d %H:%i:%s</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> GET_FORMAT(</span><span style="color:#F78C6C;">DATE</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">USA</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| GET_FORMAT(</span><span style="color:#F78C6C;">DATE</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">USA</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| %m.%d.%Y                |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATE_FORMAT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,GET_FORMAT(</span><span style="color:#F78C6C;">DATE</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">USA</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)),</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2020-01-01 00:00:00</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%Y-%m-%d</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2020-01-01 00:00:00</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%Y-%m-%d</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">2020</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;">                                    |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> warning (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br></div></div><h2 id="_5-流程控制函数" tabindex="-1">5.流程控制函数 <a class="header-anchor" href="#_5-流程控制函数" aria-hidden="true">#</a></h2><p>流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。</p><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>IF(value,value1,value2)</td><td>如果value的值为TRUE，返回value1，否则返回value2</td></tr><tr><td>IFNULL(value1, value2)</td><td>如果value1不为NULL，返回value1，否则返回value2</td></tr><tr><td>CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END</td><td>相当于Java的if...else if...else...</td></tr><tr><td>CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END</td><td>相当于Java的switch...case...</td></tr></tbody></table><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IF</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">正确</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">错误</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)    </span></span>
<span class="line"><span style="color:#89DDFF;">-&gt;</span><span style="color:#A6ACCD;">正确</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IFNULL</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">null</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Hello Word</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#89DDFF;">-&gt;</span><span style="color:#A6ACCD;">Hello Word</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1 &gt; 0</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">2 &gt; 0</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">3 &gt; 0</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">END</span></span>
<span class="line"><span style="color:#89DDFF;">-&gt;</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">我是1</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#A6ACCD;">　　</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">我是2</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">你是谁</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,salary, </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> salary</span><span style="color:#89DDFF;">&gt;=</span><span style="color:#F78C6C;">15000</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">高薪</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> salary</span><span style="color:#89DDFF;">&gt;=</span><span style="color:#F78C6C;">10000</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">潜力股</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> salary</span><span style="color:#89DDFF;">&gt;=</span><span style="color:#F78C6C;">8000</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">屌丝</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">草根</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">				  </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">  </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">描述</span><span style="color:#89DDFF;">&quot;</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees; </span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#C792EA;">oid</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">status</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">status</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">未付款</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">已付款</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">3</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">已发货</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">4</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">确认收货</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">无效订单</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">						</span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> t_order;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">yes</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&lt;=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">no</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">unknown</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">yes</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&lt;=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">no</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">unknown</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| yes                                                                  |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">---------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&lt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">yes</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">no</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">unknown</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">;  </span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&lt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">yes</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">no</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">unknown</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| unknown                                                             |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------------------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                                               </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">|                                               </span><span style="color:#89DDFF;">-</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id,</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> salary </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">+</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IFNULL</span><span style="color:#A6ACCD;">(commission_pct,</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">))</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees;</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name, job_id, salary, </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> job_id </span></span>
<span class="line"><span style="color:#A6ACCD;">				   					</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">IT_PROG</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">  </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;">  </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">10</span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;">salary</span></span>
<span class="line"><span style="color:#A6ACCD;">                   					</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ST_CLERK</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;">  </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">15</span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;">salary</span></span>
<span class="line"><span style="color:#A6ACCD;">                   					</span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">SA_REP</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">   </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;">  </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">20</span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;">salary</span></span>
<span class="line"><span style="color:#A6ACCD;">       			  					</span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;">      salary </span></span>
<span class="line"><span style="color:#A6ACCD;">       			  					</span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;">     </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">REVISED_SALARY</span><span style="color:#89DDFF;">&quot;</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">   employees;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br></div></div><img src="https://i0.hdslb.com/bfs/album/fe48aa096ee81db1677092def0f36cf1fe66e34a.png" alt="image-20221019225211935" style="zoom:150%;"><p><strong>练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。</strong></p><h2 id="_6-加密与解密函数" tabindex="-1">6. 加密与解密函数 <a class="header-anchor" href="#_6-加密与解密函数" aria-hidden="true">#</a></h2><p>加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。</p><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>PASSWORD(str)</td><td>返回字符串str的加密版本，41位长的字符串。加密结果<code>不可逆</code>，常用于用户的密码加密</td></tr><tr><td>MD5(str)</td><td>返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL</td></tr><tr><td>SHA(str)</td><td>从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。<code>SHA加密算法比MD5更加安全</code>。</td></tr><tr><td>ENCODE(value,password_seed)</td><td>返回使用password_seed作为加密密码加密value</td></tr><tr><td>DECODE(value,password_seed)</td><td>返回使用password_seed作为加密密码解密value</td></tr></tbody></table><p>注：PASSWORD(str)、ENCODE(value,password_seed)、DECODE(value,password_seed)方法在MySql8.0以上的版本已经被弃用了</p><p>可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。</p><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">PASSWORD</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">), </span><span style="color:#F78C6C;">PASSWORD</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------+----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">PASSWORD</span><span style="color:#A6ACCD;">(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)                         | </span><span style="color:#F78C6C;">PASSWORD</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NULL</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------+----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;">E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-------------------------------------------+----------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> warning (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> md5(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">123</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#89DDFF;">-&gt;</span><span style="color:#A6ACCD;">202cb962ac59075b964b07152d234b70</span></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> SHA(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">Tom123</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#89DDFF;">-&gt;</span><span style="color:#A6ACCD;">c7c506980abc31cc390a2438c90861d0f1216d50</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> ENCODE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| ENCODE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| íg　¼　ìÉ                  |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> warning (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">01</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> DECODE(ENCODE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| DECODE(ENCODE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">),</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mysql</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| mysql                                   |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">-----------------------------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> warnings (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br></div></div><h2 id="_7-mysql信息函数" tabindex="-1">7. MySQL信息函数 <a class="header-anchor" href="#_7-mysql信息函数" aria-hidden="true">#</a></h2><p>MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。</p><table><thead><tr><th>函数</th><th>用法</th></tr></thead><tbody><tr><td>VERSION()</td><td>返回当前MySQL的版本号</td></tr><tr><td>CONNECTION_ID()</td><td>返回当前MySQL服务器的连接数</td></tr><tr><td>DATABASE()，SCHEMA()</td><td>返回MySQL命令行当前所在的数据库</td></tr><tr><td>USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()</td><td>返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”</td></tr><tr><td>CHARSET(value)</td><td>返回字符串value自变量的字符集</td></tr><tr><td>COLLATION(value)</td><td>返回字符串value的比较规则</td></tr></tbody></table><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| test       |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#82AAFF;">CURRENT_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, </span><span style="color:#82AAFF;">SYSTEM_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,</span><span style="color:#82AAFF;">SESSION_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+----------------+----------------+----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">         | </span><span style="color:#82AAFF;">CURRENT_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;"> | </span><span style="color:#82AAFF;">SYSTEM_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">  | </span><span style="color:#82AAFF;">SESSION_USER</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;"> |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+----------------+----------------+----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| </span><span style="color:#F78C6C;">root</span><span style="color:#A6ACCD;">@localhost | </span><span style="color:#F78C6C;">root</span><span style="color:#A6ACCD;">@localhost | </span><span style="color:#F78C6C;">root</span><span style="color:#A6ACCD;">@localhost | </span><span style="color:#F78C6C;">root</span><span style="color:#A6ACCD;">@localhost |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+----------------+----------------+----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> CHARSET(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ABC</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| CHARSET(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ABC</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">) |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| utf8mb4        |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">----------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> COLLATION(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ABC</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| COLLATION(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ABC</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">)   |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| utf8mb4_general_ci |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">--------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br></div></div><h2 id="_8-练习" tabindex="-1">8.练习 <a class="header-anchor" href="#_8-练习" aria-hidden="true">#</a></h2><p>显示系统时间(注：日期+时间)</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> DUAL;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> employee_id, last_name, salary, salary </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">new salary</span><span style="color:#89DDFF;">&quot;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>3.将员工的姓名按首字母排序，并写出姓名的长度（length）</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name, </span><span style="color:#F78C6C;">LENGTH</span><span style="color:#A6ACCD;">(last_name) </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span></span>
<span class="line"><span style="color:#F78C6C;">ORDER BY</span><span style="color:#A6ACCD;"> last_name </span><span style="color:#F78C6C;">DESC</span><span style="color:#A6ACCD;">; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">CONCAT</span><span style="color:#A6ACCD;">(employee_id, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> , last_name , </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, salary) OUT_PUT </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序。</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">DATEDIFF</span><span style="color:#A6ACCD;">(</span><span style="color:#C792EA;">SYSDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, hire_date) </span><span style="color:#89DDFF;">/</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">365</span><span style="color:#A6ACCD;"> worked_years, </span><span style="color:#82AAFF;">DATEDIFF</span><span style="color:#A6ACCD;">(</span><span style="color:#C792EA;">SYSDATE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">, hire_date) worked_days </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span></span>
<span class="line"><span style="color:#F78C6C;">ORDER BY</span><span style="color:#A6ACCD;"> worked_years </span><span style="color:#F78C6C;">DESC</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在</p><p>1997年之后，department_id 为80或 90 或110, commission_pct不为空</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,hire_date,department_id</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> department_id </span><span style="color:#F78C6C;">IN</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">80</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">90</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">110</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> commission_pct </span><span style="color:#F78C6C;">IS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NULL</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- and hire_date &gt;= &#39;1997-01-01&#39;;  #存在着隐式转换</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- and  date_format(hire_date,&#39;%Y-%m-%d&#39;) &gt;= &#39;1997-01-01&#39;;  # 显式转换操作，格式化：日期---&gt; 字符串</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- and  date_format(hire_date,&#39;%Y&#39;) &gt;= &#39;1997&#39;;   # 显式转换操作，格式化</span></span>
<span class="line"><span style="color:#F78C6C;">AND</span><span style="color:#A6ACCD;"> hire_date </span><span style="color:#89DDFF;">&gt;=</span><span style="color:#A6ACCD;"> STR_TO_DATE(</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">1997-01-01</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">,</span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">%Y-%m-%d</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">);# 显式转换操作，解析：字符串 </span><span style="color:#676E95;font-style:italic;">----&gt; 日期</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>7.查询公司中入职超过10000天的员工姓名、入职时间</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name,hire_date </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">-- WHERE TO_DAYS(NOW()) - to_days(hire_date) &gt; 10000; </span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">DATEDIFF</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">NOW</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">,hire_date) </span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">10000</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>8.做一个查询，产生下面的结果</p><p><img src="https://i0.hdslb.com/bfs/album/848b4610708b9d014fd29400178c94888b713d1d.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">CONCAT</span><span style="color:#A6ACCD;">(last_name, </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;"> earns </span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;">(salary, </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">) , </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;"> monthly but wants </span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;">(salary </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">3</span><span style="color:#A6ACCD;">, </span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">)) </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">Dream Salary</span><span style="color:#89DDFF;">&quot;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>9.使用<strong>CASE-WHEN</strong>，按照下面的条件：</p><p><img src="https://i0.hdslb.com/bfs/album/93fc8dfdca0603f3fb09f8e43eaa4405dd72d1c5.png" alt=""></p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> last_name Last_name, job_id Job_id, </span><span style="color:#F78C6C;">CASE</span><span style="color:#A6ACCD;"> job_id </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">AD_PRES</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">A</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">													   </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ST_MAN</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">B</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">                                                       </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">IT_PROG</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">C</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">                                                       </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">SA_REP</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">D</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">                                                       </span><span style="color:#F78C6C;">WHEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">ST_CLERK</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">THEN</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">E</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">                                                       </span><span style="color:#F78C6C;">ELSE</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">F</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">                                                       </span><span style="color:#F78C6C;">END</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&quot;</span><span style="color:#C3E88D;">grade</span><span style="color:#89DDFF;">&quot;</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div></div></div><!----></main><!--[--><!--]--><footer class="VPContentDocFooter" data-v-4fe9b7bd data-v-7f892bef><a class="prev-link" href="/mysql/06.html" data-v-7f892bef><span class="desc" data-v-7f892bef><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M15,19c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4l6-6c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4L10.4,12l5.3,5.3c0.4,0.4,0.4,1,0,1.4C15.5,18.9,15.3,19,15,19z"></path></svg> Previous</span><span class="title" data-v-7f892bef>06 【多表查询】</span></a><a class="next-link" href="/mysql/08.html" data-v-7f892bef><span class="desc" data-v-7f892bef>Next <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M9,19c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l5.3-5.3L8.3,6.7c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l6,6c0.4,0.4,0.4,1,0,1.4l-6,6C9.5,18.9,9.3,19,9,19z"></path></svg></span><span class="title" data-v-7f892bef>08 【聚合函数与分组查询】</span></a></footer></div></div></div></div><div class="visually-hidden" aria-live="polite" data-v-23306c50>07 【单行函数】 has loaded</div></div></div>
    <script>__VP_HASH_MAP__ = JSON.parse("{\"about_canshu.md\":\"5cbc0865\",\"about_chahuo.md\":\"4437b8d9\",\"about_dayin.md\":\"34b5b775\",\"ajax_01.md\":\"2bc3ac36\",\"ajax_02.md\":\"2b66aa43\",\"echarts_01.md\":\"8784be9b\",\"echarts_02.md\":\"3e6ecdea\",\"echarts_03.md\":\"28ca81f0\",\"echarts_04.md\":\"d02d74fb\",\"echarts_05.md\":\"f150b054\",\"es6_01.md\":\"53b72f76\",\"es6_02.md\":\"1fc3a71b\",\"es6_03.md\":\"2b8d77d0\",\"es6_04.md\":\"19f87206\",\"es6_05.md\":\"78caf034\",\"es6_06.md\":\"f8d3c991\",\"es6_07.md\":\"324f9949\",\"es6_08.md\":\"87411498\",\"es6_09.md\":\"3486bc74\",\"es6_10.md\":\"71a60a12\",\"es6_11.md\":\"6d589e6c\",\"es6_12.md\":\"d82e096b\",\"es6_13.md\":\"78856900\",\"es6_14.md\":\"40c129d1\",\"git_01.md\":\"bf73bb28\",\"git_02.md\":\"650fa8d0\",\"git_03.md\":\"4384f718\",\"git_04.md\":\"14478c6d\",\"index.md\":\"09348a64\",\"jihe_react_index.md\":\"f57878ae\",\"jihe_reactnative_index.md\":\"c7e0faf3\",\"jihe_vue_index.md\":\"990e53d2\",\"jihe_vuerouter_index.md\":\"d28167d1\",\"jihe_vuex_index.md\":\"b9d762ab\",\"js_01.md\":\"48c6f3a8\",\"js_02.md\":\"a5183efb\",\"js_03.md\":\"5a8ce924\",\"js_04.md\":\"3db3e706\",\"js_05.md\":\"7b8951d8\",\"js_06.md\":\"dc3d579a\",\"js_07.md\":\"e7a4eda3\",\"js_08.md\":\"f75c92a3\",\"js_09.md\":\"80e86e46\",\"js_10.md\":\"646ac118\",\"js_11.md\":\"7d754050\",\"js_12.md\":\"7e6eae06\",\"js_13.md\":\"947200bc\",\"js_14.md\":\"1c0566af\",\"js_15.md\":\"d465d165\",\"js_16.md\":\"1c7c9370\",\"js_17.md\":\"71dec812\",\"js_18.md\":\"d68348c2\",\"js_19.md\":\"72c34e28\",\"js_20.md\":\"ff30fdd3\",\"js_21.md\":\"aa9bea2b\",\"js_22.md\":\"6bc45920\",\"js_23.md\":\"3131d6e6\",\"js_24.md\":\"5f12ad69\",\"js_25.md\":\"c5932cf5\",\"js_26.md\":\"b8e719df\",\"js_27.md\":\"6679728d\",\"js_28.md\":\"c9ec23e0\",\"js_29.md\":\"09376a4e\",\"js_30.md\":\"dbc798cb\",\"js_31.md\":\"283dae59\",\"js_32.md\":\"11d6ad8d\",\"js_33.md\":\"e4232cd9\",\"js_34.md\":\"a3b52a45\",\"js_35.md\":\"1477beb5\",\"js_36.md\":\"01702bb7\",\"keshitong_caigou.md\":\"c5f9edad\",\"keshitong_caiwu.md\":\"90dbd259\",\"keshitong_index.md\":\"7522f84e\",\"keshitong_xiaoshou.md\":\"b4d67695\",\"keshitong_xunjia.md\":\"49b056fe\",\"less_01.md\":\"cc6e26b8\",\"linux_01.md\":\"40a56533\",\"linux_02.md\":\"fa080f7e\",\"linux_03.md\":\"9c723c17\",\"linux_04.md\":\"c4b01f09\",\"linux_05.md\":\"cfe63746\",\"linux_06.md\":\"4e05c7c6\",\"linux_07.md\":\"2dbdda0c\",\"linux_08.md\":\"80f78478\",\"linux_09.md\":\"b9157005\",\"linux_10.md\":\"55e60bfe\",\"linux_11.md\":\"3b59a057\",\"mongodb_01.md\":\"4e9aab55\",\"mysql_01.md\":\"66f81032\",\"mysql_02.md\":\"38ff9d8a\",\"mysql_03.md\":\"4a1047e3\",\"mysql_04.md\":\"ed6dc0f6\",\"mysql_05.md\":\"fb50b511\",\"mysql_06.md\":\"4f4b1170\",\"mysql_07.md\":\"3f58b4d2\",\"mysql_08.md\":\"7e23f325\",\"mysql_09.md\":\"bef84df5\",\"mysql_10.md\":\"33e4e499\",\"mysql_11.md\":\"4e732a7d\",\"nodejs_01.md\":\"614d43a3\",\"nodejs_02.md\":\"4899f883\",\"nodejs_03.md\":\"f47d5f16\",\"nodejs_04.md\":\"7755e031\",\"nodejs_05.md\":\"ef947c59\",\"nodejs_06.md\":\"ab0a9b5a\",\"nodejs_07.md\":\"ad0e80be\",\"nodejs_08.md\":\"11027bdf\",\"nodejs_09.md\":\"4e4f9b03\",\"nodejs_10.md\":\"c4453555\",\"nodejs_11.md\":\"9f62715b\",\"nodejs_12.md\":\"1229399a\",\"nodejs_13.md\":\"e490f00c\",\"nodejs_14.md\":\"d756be55\",\"nodejs_15.md\":\"eb622fe8\",\"nodejs_16.md\":\"a5beb6c6\",\"sass_01.md\":\"0d150b1a\",\"sass_02.md\":\"8ee85f45\",\"sass_03.md\":\"7c4069d6\",\"sass_04.md\":\"85602152\",\"sass_05.md\":\"b62c38b1\",\"sass_06.md\":\"d02e3012\",\"sass_07.md\":\"edcc3b93\",\"sass_08.md\":\"9c108ef4\",\"sass_09.md\":\"3bdde859\",\"sass_10.md\":\"7eac5fed\",\"sass_11.md\":\"3d1861cf\",\"tailwindcss_01.md\":\"de90a25d\",\"tailwindcss_02.md\":\"bbfd989e\",\"tailwindcss_03.md\":\"dc351bec\",\"tailwindcss_04.md\":\"27601329\",\"test_index.md\":\"57ee7ee1\",\"test_muban.md\":\"5118e1bd\",\"test_zhongqiu.md\":\"3d9111e0\",\"typescript_01.md\":\"27d9f7e0\",\"typescript_02.md\":\"6cf9cb32\",\"typescript_03.md\":\"a851d1d2\",\"typescript_04.md\":\"954cb0d0\",\"typescript_05.md\":\"989bd8d1\",\"typescript_06.md\":\"2c90dcac\",\"vue2_01.md\":\"fd17f4d6\",\"vue2_02.md\":\"84ab761d\",\"vue2_03.md\":\"a64353d4\",\"vue2_04.md\":\"b9b0b23e\",\"vue2_05.md\":\"81a8a08d\",\"vue2_06.md\":\"2ce32e2a\",\"vue2_07.md\":\"19b50842\",\"vue2_08.md\":\"4c8868f5\",\"vue2_09.md\":\"94977e11\",\"vue2_10.md\":\"8e789ccc\",\"vue2_11.md\":\"ee9e7943\",\"vue2_12.md\":\"5249d4c5\",\"vue2_13.md\":\"048d4471\",\"vue2_14.md\":\"63be0ece\",\"vue2_15.md\":\"bd679101\",\"vue2_16.md\":\"bbe6e777\",\"vue3_01.md\":\"27d0d58b\",\"vue3_02.md\":\"22bcb9e5\",\"vue3_03.md\":\"3c52430b\",\"vue3_04.md\":\"0bb848ba\",\"vue3_06.md\":\"1cc04cca\",\"vue3_07.md\":\"28531efb\",\"vue3_08.md\":\"66ee6b02\",\"vue3_09.md\":\"9ea9285a\",\"vue3_10.md\":\"a1d6a99a\",\"vue3_11.md\":\"cb4aa0f5\",\"vue3_12.md\":\"27e13ddf\",\"vue3_13.md\":\"3976cde5\",\"vue3_14.md\":\"1b2e16e7\",\"vue3_15.md\":\"24dc5c2d\",\"vue3_16.md\":\"5efc1ed6\",\"vue3_17.md\":\"ac67e2ed\",\"yuanshen_index.md\":\"437ce691\"}")</script>
    <script type="module" async src="/assets/app.82d46cfc.js"></script>
    
  </body>
</html>